<?php
	class model_vote_management extends Zmodel{
	
		/* -- VOTE ITEM CATEGORY --*/
		
			/* SELECT */
		function selectCategory($id=null){
			$this->load->database();
			$sql=($id==null)?"SELECT vote_item_cat_id,vote_item_cat_desc FROM vote_item_category":"SELECT vote_item_cat_id,vote_item_cat_desc FROM vote_item_category WHERE vote_item_cat_id=".$id;
			$result=$this->db->query($sql);
			return $result;
		}
			/* IS EXISTING ITEM CATEGORY DESCRIPTION */
		function existingCategory($desc,$id=null){
			$this->load->database();
			$sql=($id==null)?"SELECT * FROM vote_item_category WHERE LOWER(vote_item_cat_desc)='".strtolower(pg_escape_string($desc))."'":"SELECT * FROM vote_item_category WHERE LOWER(vote_item_cat_desc)='".strtolower(pg_escape_string($desc))."' AND vote_item_cat_id!=".$id;
			$cat=$this->db->query($sql);
			$result=$cat->result();
			return count($result);
		}
			/* INSERT */
		function insertCategory($descriptioin){
			$this->load->database();
			$Z_USER = Zsession::getUserFromSession();
	    	$user_create=$Z_USER["user_id"];
			$sql="INSERT INTO vote_item_category (vote_item_cat_desc,date_create,user_create) VALUES('".pg_escape_string($descriptioin)."',now(),".$user_create.")";
			$this->db->query($sql);
		}
			/* DELETE */
		function deleteCategory($id){
			$this->load->database();
			//Get TopicID
			$sqltop="SELECT top_id FROM vote_topic WHERE vote_item_cat_id=".$id;
			$topics=$this->db->query($sqltop);
			foreach ($topics->result_array() as $topic){
				$this->deleteTopic($topic['top_id']);
			}
			
			$sqlitem="DELETE FROM vote_item WHERE vote_item_cat_id=".$id;
			//$sqltopic="DELETE FROM vote_topic WHERE vote_item_cat_id=".$id;
			$sql="DELETE FROM vote_item_category WHERE vote_item_cat_id=".$id;
			
			$this->db->query($sqlitem);
			//$this->db->query($sqltopic);
			$this->db->query($sql);
		}
			/* UPDATE */
		function updateCategory($id,$desc){
			$this->load->database();
			$Z_USER = Zsession::getUserFromSession();
	    	$user_update=$Z_USER["user_id"];
			$sql="UPDATE vote_item_category SET vote_item_cat_desc='".pg_escape_string($desc)."',date_update=now(),user_update=".$user_update." WHERE vote_item_cat_id=".$id;
			$this->db->query($sql);
		}
		
			/* -- VOTE ITEM --*/
		
			/* SELECT */
		function selectItem($id=null){
			$this->load->database();
			$sql=($id==null)?"SELECT I.vote_item_id,I.vote_item_desc,C.vote_item_cat_desc,C.vote_item_cat_id FROM vote_item AS I INNER JOIN vote_item_category AS C ON I.vote_item_cat_id=C.vote_item_cat_id":"SELECT I.vote_item_id,I.vote_item_desc,C.vote_item_cat_desc,C.vote_item_cat_id FROM vote_item AS I INNER JOIN vote_item_category AS C ON I.vote_item_cat_id=C.vote_item_cat_id WHERE I.vote_item_id=".$id;
			$result=$this->db->query($sql);
			return $result;
		}
			/* IS EXISTING ITEM DESCRIPTION */
		function existingItem($desc,$cat_id,$id=null){
			$this->load->database();
			$sql=($id==null)?"SELECT * FROM vote_item WHERE LOWER(vote_item_desc)='".strtolower(pg_escape_string($desc))."' AND vote_item_cat_id=".$cat_id:"SELECT * FROM vote_item WHERE LOWER(vote_item_desc)='".strtolower(pg_escape_string($desc))."' AND vote_item_cat_id=".$cat_id." AND vote_item_id!=".$id;
			$desc=$this->db->query($sql);
			$result=$desc->result();
			return count($result);
		}
			/* INSERT */
		function insertItem($descriptioin,$cat_id){
			$this->load->database();
			$Z_USER = Zsession::getUserFromSession();
	    	$user_create=$Z_USER["user_id"];
			$sql="INSERT INTO vote_item (vote_item_desc,vote_item_cat_id,date_create,user_create) VALUES('".pg_escape_string($descriptioin)."',".$cat_id.",now(),".$user_create.")";
			$this->db->query($sql);
		}
			/* DELETE */
		function deleteItem($id){
			$this->load->database();
			$sqlvoteitem="DELETE FROM vote_topic_item WHERE vote_item_id=".$id;
			$sql="DELETE FROM vote_item WHERE vote_item_id=".$id;

			$this->db->query($sqlvoteitem);
			$this->db->query($sql);
		}
			/* UPDATE */
		function updateItem($id,$desc,$cat_id){
			$this->load->database();
			$Z_USER = Zsession::getUserFromSession();
	    	$user_update=$Z_USER["user_id"];
			$sql="UPDATE vote_item SET vote_item_desc='".pg_escape_string($desc)."',vote_item_cat_id=".$cat_id.",date_update=now(),user_update=".$user_update." WHERE vote_item_id=".$id;
			$this->db->query($sql);
		}
		
		
		
		/* -- VOTE TOPIC --*/
		
			/* SELECT */
		function selectTopic($id=null){
			$this->load->database();
			$sql=($id==null)?"SELECT T.top_id,T.top_name,T.top_desc,T.top_item_max,C.vote_item_cat_id,C.vote_item_cat_desc FROM vote_topic AS T  INNER JOIN vote_item_category AS C ON T.vote_item_cat_id=C.vote_item_cat_id":"SELECT T.top_id,T.top_name,T.top_desc,T.top_item_max,C.vote_item_cat_id,C.vote_item_cat_desc FROM vote_topic AS T  INNER JOIN vote_item_category AS C ON T.vote_item_cat_id=C.vote_item_cat_id WHERE T.top_id=".$id;
			$result=$this->db->query($sql);
			return $result;
		}
			/* IS EXISTING ITEM CATEGORY DESCRIPTION */
		function existingTopic($top_name,$id=null){
			$this->load->database();
			$sql=($id==null)?"SELECT * FROM vote_topic WHERE LOWER(top_name)='".strtolower(pg_escape_string($top_name))."'":"SELECT * FROM vote_topic WHERE LOWER(top_name)='".strtolower(pg_escape_string($top_name))."' AND top_id!=".$id;
			$topic=$this->db->query($sql);
			$result=$topic->result();
			return count($result);
		}
			/* INSERT */
		function insertTopic($top_name,$cat_id,$top_desc,$top_item_max){
			$this->load->database();
			$Z_USER = Zsession::getUserFromSession();
	    	$user_create=$Z_USER["user_id"];
			$sql="INSERT INTO vote_topic (top_name,vote_item_cat_id,top_desc,top_item_max,date_create,user_create) VALUES('".pg_escape_string($top_name)."',".$cat_id.",'".pg_escape_string($top_desc)."',".$top_item_max.",now(),".$user_create.")";
			$this->db->query($sql);
		}
			/* DELETE */
		function deleteTopic($id){
			$this->load->database();
			
			//DELETE EVENT RELATED TO THIS TOPIC
			$sqlevent="SELECT event_id FROM vote_event WHERE top_id=".$id;
			$events=$this->db->query($sqlevent);
			foreach ($events->result_array() as $event){
				$this->deleteEvent($event['event_id']);
			}
			
			$sqlitemtopic="DELETE FROM vote_topic_item WHERE top_id=".$id;
			$sql="DELETE FROM vote_topic WHERE top_id=".$id;
			
			$this->db->query($sqlitemtopic);
			$this->db->query($sql);
		}
			/* UPDATE */
		function updateTopic($top_id,$cat_id,$top_name,$top_desc,$top_item_max){
			$this->load->database();
			$Z_USER = Zsession::getUserFromSession();
	    	$user_update=$Z_USER["user_id"];
			$sql="UPDATE vote_topic SET top_name='".pg_escape_string($top_name)."',vote_item_cat_id=".$cat_id.",top_desc='".pg_escape_string($top_desc)."',top_item_max=".$top_item_max.",date_update=now(),user_update=".$user_update." WHERE top_id=".$top_id;
			$this->db->query($sql);
		}
		
			/* SELECT Item By Category */
		function selectItembyCategory($cat_id){
			$this->load->database();
			$sql="SELECT vote_item_id,vote_item_desc FROM vote_item WHERE vote_item_cat_id=".$cat_id;
			$result=$this->db->query($sql);
			return $result;
		}
			/* SELECT Item By Topic */
		function selectTopicItem($top_id){
			$this->load->database();
			$sql="SELECT * FROM vote_topic_item WHERE top_id=".$top_id;
			$result=$this->db->query($sql);
			return $result;
		}
		
			/* Assign Topic Item */
		function insertTopicItem($top_id,$item_id){
			$this->load->database();
			$sql="INSERT INTO vote_topic_item (top_id,vote_item_id) VALUES(".$top_id.",".$item_id.")";
			$this->db->query($sql);
		}
			/* Delete Topic Item */
		function deleteTopicItem($top_id){
			$this->load->database();
			$sqldel="DELETE FROM vote_topic_item WHERE top_id=".$top_id;
			$this->db->query($sqldel);
		}
		
		
			/* VOTE EVENT */
		
		function assignVoteEvent($top_id,$start_date,$end_date){
			$this->load->database();
			$sql="INSERT INTO vote_topic_item (top_id,vote_item_id) VALUES(".$top_id.",".$item_id.")";
			$this->db->query($sql);
		}
		
			/* SELECT */
		function selectEvent($id=null){
			$this->load->database();
			$sql=($id==null)?"SELECT E.event_id,E.top_id,T.top_name,T.top_desc,E.start_date,E.end_date FROM vote_event AS E INNER JOIN vote_topic AS T ON E.top_id=T.top_id":"SELECT E.event_id,E.top_id,T.top_name,T.top_desc,E.start_date,E.end_date FROM vote_event AS E INNER JOIN vote_topic AS T ON E.top_id=T.top_id WHERE event_id=".$id;
			$result=$this->db->query($sql);
			return $result;
		}
			/* DELETE */
		function deleteEvent($event_id){
			$this->load->database();
			$sqluserevent="DELETE FROM user_vote_event WHERE event_id=".$event_id;
			$sqlevent="DELETE FROM vote_event WHERE event_id=".$event_id;
			
			$this->db->query($sqluserevent);
			$this->db->query($sqlevent);
		}
			/* INSERT */
		function insertEvent($top_id,$start_date,$end_date){
			$this->load->database();
			$Z_USER = Zsession::getUserFromSession();
	    	$user_create=$Z_USER["user_id"];
			$sql="INSERT INTO vote_event (top_id,start_date,end_date,date_create,user_create) VALUES(".$top_id.",'".$start_date."','".$end_date."',now(),".$user_create.")";
			$this->db->query($sql);
		}
			/* IS EXISTING EVENT */
		function existingEvent($top_id,$start_date,$end_date,$event_id=null){
			$this->load->database();
			$sql=($event_id==null)?"SELECT * FROM vote_event WHERE top_id=".$top_id." AND start_date='".$start_date."' AND end_date='".$end_date."'":"SELECT * FROM vote_event WHERE top_id=".$top_id." AND start_date='".$start_date."' AND end_date='".$end_date."' AND event_id<>".$event_id;
			$event=$this->db->query($sql);
			$result=$event->result();
			return count($result);
		}
		
				/* UPDATE */
		function updateEvent($event_id,$top_id,$start_date,$end_date){
			$this->load->database();
			$Z_USER = Zsession::getUserFromSession();
	    	$user_update=$Z_USER["user_id"];
			$sql="UPDATE vote_event SET top_id=".$top_id.", start_date='".$start_date."',end_date='".$end_date."',date_update=now(),user_update=".$user_update." WHERE event_id=".$event_id;
			$this->db->query($sql);
		}
	}
?>